﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Security.Cryptography;
using WebKpiPage.Data.Common;
using Model.Sysmng;

namespace WebKpiPage.Data.Sysmng
{
    public static class SysmngDB
    {
        #region 获取用户信息列表
        /// <summary>
        /// 获取用户信息列表
        /// </summary>
        /// <param name="SearchType"></param>
        /// <param name="Content"></param>
        /// <returns></returns>
        public static DataTable GetUserInfoList(int SearchType,string Content)
        {
            SqlConnection conn = ConnMng.GetConnection();
            if (conn == null || conn.State != System.Data.ConnectionState.Open)
            {
                LogSystem.LogInfo("AddGongDanRecord  conn == null || conn.State != System.Data.ConnectionState.Open");
                return null;
            }
            try
            {
                string commandText = "MTNOH_GZ_APP.dbo.mtsp_查询_用户列表信息";
                int count = 2;
                SqlParameter[] sp = new SqlParameter[count];
                sp[--count] = new SqlParameter("@SearchType", SearchType);
                sp[--count] = new SqlParameter("@Content", Content);
                SqlDataAdapter ad = SqlHelper.ExecuteDataAdapter(conn, CommandType.StoredProcedure, commandText, sp);
                DataTable dt = new DataTable();
                ad.Fill(dt);
                return dt;
            }
            catch (Exception e)
            {
                LogSystem.LogError("GetUserInfoList", e);
                return null;
            }
            finally
            {
                ConnMng.ReleaseConnection(conn);
            }
        }
        #endregion

        #region 加载用户详细信息界面信息
        /// <summary>
        /// 加载用户详细信息界面信息
        /// </summary>
        /// <returns></returns>
        public static DataSet GetUserBaseInfo()
        {
            SqlConnection conn = ConnMng.GetConnection();
            if (conn == null || conn.State != System.Data.ConnectionState.Open)
            {
                LogSystem.LogInfo("AddGongDanRecord  conn == null || conn.State != System.Data.ConnectionState.Open");
                return null;
            }
            try
            {
                string commandText = "MTNOH_GZ_APP.dbo.mtsp_查询_用户信息_界面";
                SqlDataAdapter ad = SqlHelper.ExecuteDataAdapter(conn, CommandType.StoredProcedure, commandText);
                DataSet ds = new DataSet();
                ad.Fill(ds);
                return ds;
            }
            catch (Exception e)
            {
                LogSystem.LogError("GetInfoForInitPage", e);
                return null;
            }
            finally
            {
                ConnMng.ReleaseConnection(conn);
            }
        }
        #endregion

        #region 删除客户记录
        /// <summary>
        /// 删除客户记录
        /// </summary>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public static string DeleteUserBaseInfo(string UserID)
        {
                string rst="";
            SqlConnection conn = ConnMng.GetConnection();
            if (conn == null || conn.State != System.Data.ConnectionState.Open)
            {
                LogSystem.LogInfo("AddGongDanRecord  conn == null || conn.State != System.Data.ConnectionState.Open");
                return rst="数据库连接失败。";
            }
            try
            {
                string commandText = @"update MTNOH_GZ_APP.dbo.T_User 
                                    set Flag=0 
                                    where ID='" + UserID + "'";
                int a = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText);
                if(a!=null && a<0)
                {
                    rst = "删除失败。";
                }
                return rst;
            }
            catch (Exception e)
            {
                LogSystem.LogError("DeleteUserBaseInfo", e);
                return rst = e.Message.ToString();
            }
            finally
            {
                ConnMng.ReleaseConnection(conn);
            }
        }
        #endregion

        #region 查询角色列表
        /// <summary>
        /// 查询角色列表
        /// </summary>
        /// <param name="RoleName"></param>
        /// <returns></returns>
        public static DataTable GetRoleInfoList(string RoleName)
        {
            SqlConnection conn = ConnMng.GetConnection();
            if (conn == null || conn.State != System.Data.ConnectionState.Open)
            {
                LogSystem.LogInfo("AddGongDanRecord  conn == null || conn.State != System.Data.ConnectionState.Open");
                return null;
            }
            try
            {
                string commandText = @"select role_id,role_name,role_desc 
                                      from MTNOH_GZ_APP.dbo.C_ROLE 
                                      where role_name like '%" + RoleName + "%'";
                SqlDataAdapter ad = SqlHelper.ExecuteDataAdapter(conn, CommandType.Text, commandText);
                DataTable dt = new DataTable();
                ad.Fill(dt);
                return dt;
            }
            catch (Exception e)
            {
                LogSystem.LogError("GetRoleInfoList", e);
                return null;
            }
            finally
            {
                ConnMng.ReleaseConnection(conn);
            }
        }
        #endregion

        #region 删除角色信息
        /// <summary>
        /// 删除角色信息
        /// </summary>
        /// <param name="RoleID"></param>
        /// <returns></returns>
        public static string DeleteRoleBaseInfo(int RoleID)
        {
            string rst = "";
            SqlConnection conn = ConnMng.GetConnection();
            if (conn == null || conn.State != System.Data.ConnectionState.Open)
            {
                LogSystem.LogInfo("AddGongDanRecord  conn == null || conn.State != System.Data.ConnectionState.Open");
                return rst = "数据库连接失败。";
            }
            try
            {
                string commandText = @"delete from MTNOH_GZ_APP.dbo.C_ROLE 
                                        where role_id=" + RoleID 
                                        + @" delete from dbo.C_Role_Menu 
                                        where role_id=" + RoleID;
                int a = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText);
                if (a != null && a < 0)
                {
                    rst = "删除失败。";
                }
                return rst;
            }
            catch (Exception e)
            {
                LogSystem.LogError("DeleteUserBaseInfo", e);
                return rst = e.Message.ToString();
            }
            finally
            {
                ConnMng.ReleaseConnection(conn);
            }
        }
        #endregion

        #region 编辑用户信息
        public static string EditUserInfo(UserInfoItem userInfo)
        {
            string rst = "";
            SqlConnection conn = ConnMng.GetConnection();
            if (conn == null || conn.State != System.Data.ConnectionState.Open)
            {
                LogSystem.LogInfo("AddGongDanRecord  conn == null || conn.State != System.Data.ConnectionState.Open");
                return rst = "数据库连接失败。";
            }
            try
            {
                string commandText = "MTNOH_GZ_APP.dbo.mtsp_编辑_用户信息";
                int count = 10;
                SqlParameter[] sp = new SqlParameter[count];
                sp[--count] = new SqlParameter("@UserId", userInfo.UserId);
                sp[--count] = new SqlParameter("@Name", userInfo.Name);
                sp[--count] = new SqlParameter("@UserName", userInfo.UserName);
                sp[--count] = new SqlParameter("@Gender", userInfo.Gender);
                sp[--count] = new SqlParameter("@Role_id", userInfo.Role_id);
                sp[--count] = new SqlParameter("@Company_id", userInfo.Company_id);
                sp[--count] = new SqlParameter("@Group_id", userInfo.Group_id);
                sp[--count] = new SqlParameter("@Position_id", userInfo.Position_id);
                sp[--count] = new SqlParameter("@Phone", userInfo.Phone);
                sp[--count] = new SqlParameter("@Email", userInfo.Email);
                int a = SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, commandText, sp);
                if (a < 0)
                {
                    rst = "编辑数据失败。";
                }
                return rst;
            }
            catch (Exception e)
            {
                LogSystem.LogError("GetUserInfoList", e);
                return e.Message.ToString() ;
            }
            finally
            {
                ConnMng.ReleaseConnection(conn);
            }
        }
        #endregion

        #region 编辑角色信息
        public static string EditRoleInfo(RoleInfoItem roleInfo)
        {
            string rst = "";
            SqlConnection conn = ConnMng.GetConnection();
            if (conn == null || conn.State != System.Data.ConnectionState.Open)
            {
                LogSystem.LogInfo("AddGongDanRecord  conn == null || conn.State != System.Data.ConnectionState.Open");
                return rst = "数据库连接失败。";
            }
            try
            {
                string commandText = "MTNOH_GZ_APP.dbo.mtsp_编辑_角色信息";
                int count = 4;
                SqlParameter[] sp = new SqlParameter[count];
                sp[--count] = new SqlParameter("@RoleId", roleInfo.RoleId);
                sp[--count] = new SqlParameter("@RoleName", roleInfo.RoleName);
                sp[--count] = new SqlParameter("@RoleDescribe", roleInfo.RoleDescribe);
                sp[--count] = new SqlParameter("@MenuIdsStr", roleInfo.MenuIdsStr);
                int a = SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, commandText, sp);
                if (a < 0)
                {
                    rst = "编辑数据失败。";
                }
                return rst;
            }
            catch (Exception e)
            {
                LogSystem.LogError("GetUserInfoList", e);
                return e.Message.ToString();
            }
            finally
            {
                ConnMng.ReleaseConnection(conn);
            }
        }
        #endregion 

        #region 获取目录列表，用于加载角色分配目录树
        /// <summary>
        /// 获取目录列表，用于加载角色分配目录树
        /// </summary>
        /// <returns></returns>
        public static DataTable GetMenuList()
        {
            SqlConnection conn = ConnMng.GetConnection();
            if (conn == null || conn.State != System.Data.ConnectionState.Open)
            {
                LogSystem.LogInfo("AddGongDanRecord  conn == null || conn.State != System.Data.ConnectionState.Open");
                return null;
            }
            try
            {
                string commandText = @"select id,parentId,menu_name,menu_desc 
                                        from MTNOH_GZ_APP.dbo.C_Menu";
                SqlDataAdapter ad = SqlHelper.ExecuteDataAdapter(conn, CommandType.Text, commandText);
                DataTable dt = new DataTable();
                ad.Fill(dt);
                return dt;
            }
            catch (Exception e)
            {
                LogSystem.LogError("GetMenuList", e);
                return null;
            }
            finally
            {
                ConnMng.ReleaseConnection(conn);
            }
        }
        #endregion

        #region 获取角色基础信息，填充角色详情页面
        /// <summary>
        /// 获取角色基础信息，填充角色详情页面
        /// </summary>
        /// <returns></returns>
        public static DataSet GetRoleBaseInfo(int RoleId)
        {
            SqlConnection conn = ConnMng.GetConnection();
            if (conn == null || conn.State != System.Data.ConnectionState.Open)
            {
                LogSystem.LogInfo("AddGongDanRecord  conn == null || conn.State != System.Data.ConnectionState.Open");
                return null;
            }
            try
            {
                string commandText = "MTNOH_GZ_APP.dbo.mtsp_查询_角色详细信息";
                int count = 1;
                SqlParameter[] sp = new SqlParameter[count];
                sp[--count] = new SqlParameter("@RoleId", RoleId);
                SqlDataAdapter ad = SqlHelper.ExecuteDataAdapter(conn, CommandType.StoredProcedure, commandText, sp);
                DataSet ds = new DataSet();
                ad.Fill(ds);
                return ds;
            }
            catch (Exception e)
            {
                LogSystem.LogError("GetRoleBaseInfo", e);
                return null;
            }
            finally
            {
                ConnMng.ReleaseConnection(conn);
            }
        }
        /// <summary>
        /// 获取系统字典表
        /// </summary>
        /// <returns></returns>
        public static DataTable GetDicTable()
        {
            SqlConnection conn = ConnMng.GetConnection();
            if (conn == null || conn.State != System.Data.ConnectionState.Open)
            {
                LogSystem.LogInfo("AddGongDanRecord  conn == null || conn.State != System.Data.ConnectionState.Open");
                return null;
            }
            try
            {
                string commandText = @"select t.ID,t.ParentID,t.Name,t.Description,t.OrderID,t.ParentName from T_Dictionary t";
                DataSet ds = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText);
                return ds.Tables[0];
            }
            catch (Exception e)
            {
                LogSystem.LogError("GetMenuList", e);
                return null;
            }
            finally
            {
                ConnMng.ReleaseConnection(conn);
            }
        }
        #endregion

        #region 手机系统_查询_日志信息
        /// <summary>
        /// mtsp_手机系统_查询_日志信息
        /// </summary>
        /// <returns></returns>
        public static DataSet GetPhoneSysLogInfo(string beginTime, string endTime, int currentpage, int pageSize)
        {
            SqlConnection conn = ConnMng.GetConnection();
            if (conn == null || conn.State != System.Data.ConnectionState.Open)
            {
                LogSystem.LogInfo("AddGongDanRecord  conn == null || conn.State != System.Data.ConnectionState.Open");
                return null;
            }
            try
            {
                //dbo.MTSP_通用存储过程分页 '''order_num''=ROW_NUMBER()over(order by Time desc),
                //                    convert(varchar(20),Time,120) as Time
                //                    ,t.姓名
                //                    ,Login_Code
                //                    ,IP
                //                    ,URL
                //                    ,Func_Name'
                //                    ,'GZMT.dbo.mtnos_tb_visit_log v,GZMT.dbo.T_用户 t'
                //                    ,'v.Login_Code=t.登录名  DATEDIFF(D,@beginTime,Time)>=0 and DATEDIFF(D,Time,@endTime)>=0	'
                //                    ,'v.ID'
                //                    ,' order by v.ID desc'
                //                    ,pageSize
                //                    ,currentpage
    //            @select_list varchar(1000)='*',--不需要select
    //@table_name varchar(500),
    //@where varchar(1000)='',--不需要where
    //@primary_key varchar(100),--当是表联合时，加表名前缀.
    //@order_by varchar(200),--需要完整的子句 order by ...
    //@page_size smallint=20,--每页记录
    //@page_index int=1--页索引
                string selectList = "'order_num'=ROW_NUMBER()over(order by Time desc),"
                                    + "convert(varchar(20),Time,120) as Time"
                                    + ",t.姓名"
                                    + ",Login_Code"
                                    + ",IP"
                                    + ",URL"
                                    + ",Func_Name";
                string tableName = " GZMT.dbo.mtnos_tb_visit_log v,GZMT.dbo.T_用户 t ";
                string where = " v.Login_Code=t.登录名 and DATEDIFF(D," + "'" + beginTime + "'" + ",Time)>=0 and DATEDIFF(D,Time," + "'" + endTime + "'" + ")>=0 ";
                string primaryKey = " v.ID ";
                string orderBy = " order by v.ID desc ";
                string commandText = "MTNOH_GZ_APP.dbo.MTSP_通用存储过程分页";// v.Login_Code=t.登录名  DATEDIFF(D,2012-11-01,Time)>=0 and DATEDIFF(D,Time,2012-11-05)>=0
                int count = 7;
                SqlParameter[] sp = new SqlParameter[count];
                sp[--count] = new SqlParameter("@select_list", selectList);
                sp[--count] = new SqlParameter("@table_name", tableName);
                sp[--count] = new SqlParameter("@where", where);
                sp[--count] = new SqlParameter("@primary_key", primaryKey);
                sp[--count] = new SqlParameter("@order_by", orderBy);
                sp[--count] = new SqlParameter("@page_size", pageSize);
                sp[--count] = new SqlParameter("@page_index", currentpage);
                DataSet ds = SqlHelper.ExecuteDataset(conn, CommandType.StoredProcedure, commandText, sp);
                return ds;
            }
            catch (Exception e)
            {
                LogSystem.LogError("GetRoleBaseInfo", e);
                return null;
            }
            finally
            {
                ConnMng.ReleaseConnection(conn);
            }
        }
        #endregion

        #region 手机系统_统计_登录信息
        /// <summary>
        /// 手机系统_统计_登录信息
        /// </summary>
        /// <returns></returns>
        public static DataSet StatisticsPhoneSysLoginInfo()
        {
            SqlConnection conn = ConnMng.GetConnection();
            if (conn == null || conn.State != System.Data.ConnectionState.Open)
            {
                LogSystem.LogInfo("AddGongDanRecord  conn == null || conn.State != System.Data.ConnectionState.Open");
                return null;
            }
            try
            {
                string commandText = "MTNOH_GZ_APP.dbo.mtsp_手机系统_统计_登录信息";
                SqlDataAdapter ad = SqlHelper.ExecuteDataAdapter(conn, CommandType.StoredProcedure, commandText);
                DataSet ds = new DataSet();
                ad.Fill(ds);
                return ds;
            }
            catch (Exception e)
            {
                LogSystem.LogError("GetRoleBaseInfo", e);
                return null;
            }
            finally
            {
                ConnMng.ReleaseConnection(conn);
            }
        }
        #endregion

        #region 根据用户姓名获取模块访问统计
        /// <summary>
        /// 根据用户姓名获取模块访问统计
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public static DataTable GetStatisInfoByUserName(string loginCode)
        {
            SqlConnection conn = ConnMng.GetConnection();
            if (conn == null || conn.State != System.Data.ConnectionState.Open)
            {
                LogSystem.LogInfo("AddGongDanRecord  conn == null || conn.State != System.Data.ConnectionState.Open");
                return null;
            }
            try
            {
                string commandText = "select "
                    + " ROW_NUMBER()over(order by COUNT(func_Name) desc) as order_num"
                    +",Func_Name  "
                    + ",COUNT(func_Name) 访问次数 "
                    + "  from  GZMT.dbo.mtnos_tb_visit_log l "
                   + " where l.Login_Code=" + "'" + loginCode + "'"
                    + " group by Func_Name"
                    + " order by 访问次数 ,order_num desc";
                DataSet ds = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText);
                if (ds != null && ds.Tables.Count > 0)
                {
                    return ds.Tables[0];
                }
                return null;
            }
            catch (Exception e)
            {
                LogSystem.LogError("GetRoleBaseInfo", e);
                return null;
            }
            finally
            {
                ConnMng.ReleaseConnection(conn);
            }
        }
        #endregion

        #region 根据功能名称获取用户访问统计
        /// <summary>
        /// 根据功能名称获取用户访问统计
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public static DataTable GetStatisInfoByFuncName(string funcName)
        {
            SqlConnection conn = ConnMng.GetConnection();
            if (conn == null || conn.State != System.Data.ConnectionState.Open)
            {
                LogSystem.LogInfo("AddGongDanRecord  conn == null || conn.State != System.Data.ConnectionState.Open");
                return null;
            }
            try
            {
                string commandText = "select "
                +" ROW_NUMBER()over(order by COUNT(l.Login_Code) desc) as order_num"
                +",u.姓名  "
                + ",COUNT(l.Login_Code) 访问次数 "
                + "  from  GZMT.dbo.mtnos_tb_visit_log l "
                +" join GZMT.dbo.T_用户 u "
                +" on l.Msisdn=u.手机号码"
               + " and l.Func_Name=" + "'"+funcName + "'"
                +" group by u.姓名"
                + " order by 访问次数 ,order_num desc";
               
                DataSet ds = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText);
               if (ds!=null&&ds.Tables.Count>0)
               {
                   return ds.Tables[0];
               }
                return null;
            }
            catch (Exception e)
            {
                LogSystem.LogError("GetRoleBaseInfo", e);
                return null;
            }
            finally
            {
                ConnMng.ReleaseConnection(conn);
            }
        }
        #endregion

        #region 查询_参数设置_界面
        /// <summary>
        /// mtsp_查询_参数设置_界面
        /// </summary>
        /// <returns></returns>
        public static DataSet GetSysParamInit()
        {
            SqlConnection conn = ConnMng.GetConnection();
            if (conn == null || conn.State != System.Data.ConnectionState.Open)
            {
                LogSystem.LogInfo("AddGongDanRecord  conn == null || conn.State != System.Data.ConnectionState.Open");
                return null;
            }
            try
            {
                string commandText = "MTNOH_GZ_APP.dbo.mtsp_查询_参数设置_界面";
                DataSet ds = SqlHelper.ExecuteDataset(conn, CommandType.StoredProcedure, commandText);
                if (ds != null && ds.Tables.Count > 0)
                {
                    return ds;
                }
                return null;
            }
            catch (Exception e)
            {
                LogSystem.LogError("GetSysParamInit", e);
                return null;
            }
            finally
            {
                ConnMng.ReleaseConnection(conn);
            }
        }
        #endregion

        #region 编辑系统参数设置信息
        public static string SetSysParamInfo(SetParamItem SysParamItem)
        {
            string rst = "";
            SqlConnection conn = ConnMng.GetConnection();
            if (conn == null || conn.State != System.Data.ConnectionState.Open)
            {
                LogSystem.LogInfo("AddGongDanRecord  conn == null || conn.State != System.Data.ConnectionState.Open");
                return rst = "数据库连接失败。";
            }
            try
            {
                string commandText = "MTNOH_GZ_APP.dbo.mtsp_编辑_系统参数";
                int count = 7;
                SqlParameter[] sp = new SqlParameter[count];
                sp[--count] = new SqlParameter("@savePartIndex", SysParamItem.savePartIndex);
                sp[--count] = new SqlParameter("@chaoShiGuan", SysParamItem.chaoShiGuan);
                sp[--count] = new SqlParameter("@chaoShiRen", SysParamItem.chaoShiRen);
                sp[--count] = new SqlParameter("@pingGuGuan", SysParamItem.pingGuGuan);
                sp[--count] = new SqlParameter("@pingGuRen", SysParamItem.pingGuRen);
                sp[--count] = new SqlParameter("@renYuanID", SysParamItem.renYuanID);
                sp[--count] = new SqlParameter("@pianQuIds", SysParamItem.pianQuIds);
                int a = SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, commandText, sp);
                if (a < 0)
                {
                    rst = "编辑数据失败。";
                }
                return rst;
            }
            catch (Exception e)
            {
                LogSystem.LogError("SetSysParamInfo", e);
                return e.Message.ToString();
            }
            finally
            {
                ConnMng.ReleaseConnection(conn);
            }
        }
        #endregion 

        #region 查询_参数设置_片区列表
        /// <summary>
        /// 查询_参数设置_片区列表
        /// </summary>
        /// <returns></returns>
        public static DataTable GetSysParamPianQuInfo(string peopleId)
        {
            SqlConnection conn = ConnMng.GetConnection();
            if (conn == null || conn.State != System.Data.ConnectionState.Open)
            {
                LogSystem.LogInfo("AddGongDanRecord  conn == null || conn.State != System.Data.ConnectionState.Open");
                return null;
            }
            try
            {
                string commandText = @"select * from MTNOH_GZ_APP.dbo.Base_片区_责任人表 
	                                    where FK_责任人_ID='" + peopleId + "'";
                DataSet ds = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText);
                if (ds != null && ds.Tables.Count > 0)
                {
                    return ds.Tables[0];
                }
                return null;
            }
            catch (Exception e)
            {
                LogSystem.LogError("GetSysParamPianQuInfo", e);
                return null;
            }
            finally
            {
                ConnMng.ReleaseConnection(conn);
            }
        }
        #endregion
    }
}
